﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Threading;

namespace HSIA
{
    public partial class Frm_DataBR : Form
    {
        public Frm_DataBR()
        {
            InitializeComponent();
        }

        delegate void Worked();

        private void Finished()
        {
            if (!progressBar1.InvokeRequired)
            {
                progressBar1.Value = 100;
                if (PublicFunc.sLanguage == "CN")
                    label1.Text = "数据库" + sTitle + "成功!";
                else
                    label1.Text = "Database " + sTitle + " Succeed!";
                button1.Enabled = button2.Enabled = true;
            }
            else
            {
                Worked wd = new Worked(Finished);
                Invoke(wd);
                //progressBar1.Value = 100;
            }
        }

        string sSQL = "";
        string sTitle = "";
        string sSrcFile = "";
        //string sWork = "";
        string strConn = "";//"Data Source={0};Initial Catalog=Master;Integrated Security=SSPI;";
        //int iVersion = DatabaseObject.iVersion; 
        string sRet = "";
        string sInfo = "正在进行数据库{0} . . .";

        private void miBackup_Click(object sender, EventArgs e)
        {
            progressBar1.Value = 0; //label1.Text = "准备进行数据库操作 . . .";
            DialogResult dlr = saveFileDialog1.ShowDialog();
            if (dlr == DialogResult.OK)
            {
                sSrcFile = saveFileDialog1.FileName;
                sSQL = "BACKUP DATABASE " + PublicFunc.sDatabase + " TO DISK = '" + saveFileDialog1.FileName + "'";
                sTitle = (PublicFunc.sLanguage == "CN") ? "备份" : "Backup";
                label1.Text = string.Format(sInfo, sTitle);
                button1.Enabled = button2.Enabled = false;
                Thread td = new Thread(new ThreadStart(Work));
                td.Start();
                timer1.Enabled = true;
            }
            else
                sSrcFile = "";
        }

        private void Work()
        {
            sRet = ExcuteSQL(sSQL);
            timer1.Enabled = false;
            Finished();
            //progressBar1.Value = 100;
        }

        //private void SetProcBar()
        //{
        //    progressBar1.Value = progressBar1.Value + 10;
        //    this.Refresh();
        //}

        //private void SetProcBar(bool bl)
        //{
        //    if (bl)
        //    {
        //        progressBar1.Value = progressBar1.Maximum;
        //        label1.Text = "数据库" + sTitle + "成功!";
        //    }
        //    else
        //    {
        //        progressBar1.Value = 0;
        //        label1.Text = "数据库" + sTitle + "失败!";
        //    }
        //    this.Refresh();
        //}

        //private void BackupDatabase()
        //{
        //    try
        //    {
        //        SetProcBar();
        //        string sSQL = "select   [filename]   from   master..sysdatabases   where   name= '" + sDatabase + "'";
        //        DataTable dt = dbo.GetDataTableFromMaster(sSQL, "sysdatabases");
        //        if (dt.Rows.Count == 0)
        //        {
        //            MessageBox.Show("数据库有错误,请与管理员联系!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //            return;
        //        }
        //        SetProcBar();
        //        string sDBFile = dt.Rows[0]["filename"].ToString();
        //        string sSQLs = "EXEC sp_detach_db '"+sDatabase+"', 'true';";
        //        string sRet = dbo.ExcuteSQLByMaster(sSQLs);
        //        SetProcBar();
        //        if (sRet == "OK")
        //        {
        //            File.Copy(sDBFile, sSrcFile);
        //            SetProcBar();
        //            sSQLs = "EXEC sp_attach_db @dbname = N'AMATE', @filename1 = N'" + sDBFile + "';";
        //            sRet = dbo.ExcuteSQLByMaster(sSQLs);
        //            SetProcBar(true);
        //            if (sRet == "OK") sRet = "数据库" + sTitle + "成功!"; else SetProcBar(false);
        //            MessageBox.Show(sRet);
        //        }
        //        else
        //        {
        //            SetProcBar(false);
        //            sRet = "数据库备份失败,请关掉所有相关窗口,然后执行还原操作!";
        //            DialogResult dd = MessageBox.Show(sRet, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        }
        //    }
        //    catch
        //    {
        //        SetProcBar(false);
        //    }
        //}

        //private void RestoreDatabase()
        //{
        //    try
        //    {
        //        SetProcBar();
        //        string sSQL = "select   [filename]   from   sysdatabases   where   name= '"+sDatabase+"'";
        //        DataTable dt = dbo.GetDataTableFromMaster(sSQL, "aa");
        //        SetProcBar();
        //        if (dt.Rows.Count == 0)
        //        {
        //            MessageBox.Show("数据库有错误,请与管理员联系!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //            return;
        //        }
        //        string sDBFile = dt.Rows[0]["filename"].ToString();
        //        string sSQLs = "EXEC sp_detach_db '"+sDatabase+"', 'true';";
        //        string sRet = dbo.ExcuteSQLByMaster(sSQLs);
        //        SetProcBar();
        //        string sDir = Path.GetDirectoryName(sDBFile);
        //        string sMDF = sDBFile;
        //        string sLDF = sDBFile.Replace(".mdf", "_log.LDF");
        //        string sTime = DateTime.Now.ToString("yyyyMMddHHmmss.");
        //        string sBackupMDF = sMDF.Replace(".", sTime);
        //        string sBackupLDF = sLDF.Replace(".", sTime);
        //        SetProcBar();
        //        //dbo.ExcuteSQLByMaster(sSQL);

        //        if (sRet == "OK")
        //        {
        //            SetProcBar();
        //            File.Move(sDBFile, sBackupMDF);
        //            File.Move(sLDF, sBackupLDF);
        //            SetProcBar();
        //            //File.Delete(sDBFile);
        //            //File.Delete(sDBFile.Replace("AMATE.mdf", "AMATE_log.LDF"));
        //            try
        //            {
        //                File.Copy(sSrcFile, sDBFile);
        //                SetProcBar();
        //                sSQLs = "EXEC sp_attach_db @dbname = N'AMATE', @filename1 = N'" + sDBFile + "';";
        //                sRet = dbo.ExcuteSQLByMaster(sSQLs);
        //                SetProcBar(true);
        //                if (sRet == "OK") sRet = "数据库还原成功!";
        //                MessageBox.Show(sRet);
        //                File.Delete(sBackupMDF); File.Delete(sBackupLDF);
        //            }
        //            catch
        //            {
        //                File.Delete(sDBFile);
        //                //string sDir = Path.GetDirectoryName(sDBFile);
        //                File.Move(sBackupMDF, sMDF);
        //                //string sLDF = sDBFile.Replace("AMATE.mdf", "AMATE_log.LDF");
        //                File.Move(sBackupLDF, sLDF);
        //                sRet = dbo.ExcuteSQLByMaster(sSQLs);
        //                sRet = "数据库备份失败,请检查备份文是否有效数据库文件!";
        //                SetProcBar(false);
        //                DialogResult dd = MessageBox.Show(sRet, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //            }
        //        }
        //        else
        //        {
        //            SetProcBar(false);
        //            sRet = "数据库备份失败,请关掉所有相关窗口,然后执行还原操作!";
        //            DialogResult dd = MessageBox.Show(sRet, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //        }
        //    }
        //    catch { SetProcBar(false); }
        //}

        private void miRestore_Click(object sender, EventArgs e)
        {
            progressBar1.Value = 0;
            //label1.Text = "准备进行数据库操作 . . .";
            string ms = (PublicFunc.sLanguage == "CN") ? "确实要还原数据库吗?当前数据库所有内容将被清空且不可恢复!" : "Confirm to restore database? All content in the existing database will be purged beyond retrieve!";
            DialogResult dllr = MessageBox.Show(ms, "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dllr == DialogResult.No) return;

            DialogResult dlr = openFileDialog1.ShowDialog();
            if (dlr == DialogResult.OK)
            {
                sSrcFile = openFileDialog1.FileName;
                sSQL = "RESTORE DATABASE " + PublicFunc.sDatabase + " FROM DISK = '" + openFileDialog1.FileName + "'";
                if (PublicFunc.sLanguage == "CN") sTitle = "还原"; else sTitle = "Restore";
                label1.Text = string.Format(sInfo, sTitle);
                button1.Enabled = button2.Enabled = false;

                ExcuteSQL(sDelSP);
                ExcuteSQL(sCreateSP);
                ExcuteSQL("StopLogin '" + PublicFunc.sDatabase + "'");

                Thread td = new Thread(new ThreadStart(Work));
                td.Start();
                timer1.Enabled = true;

                //label1.Text = "正在进行数据库" + sTitle + " . . .";
                //this.Refresh();
                //RestoreDatabase();            
            }
        }
        //DatabaseObject dbo;
        private string ExcuteSQL(string strSQL)
        {

            if (strSQL == "")
            {
                string ms = (PublicFunc.sLanguage == "CN") ? "您当前操作的窗体没有内容被更新!" : "Your form is not the content of the current operation has been updated!";
                return ms;
            }
            //char cSpliter = ';';
            // string[] sl = strSQL.Split(new char[] { cSpliter }, StringSplitOptions.RemoveEmptyEntries);

            string strRet = "";
            SqlConnection sc = new SqlConnection(strConn);

            try
            {
                sc.Open();
                //SqlTransaction st = sc.BeginTransaction();
                SqlCommand cd = new SqlCommand();
                cd.Connection = sc;
                //SqlCommand cd = sc.CreateCommand();
                //cd.Transaction = st;

                //foreach (string str in sl)
                //{
                //    //SqlCommand cd = new SqlCommand(str, sc);
                //    if (str == "") continue;
                cd.CommandType = CommandType.Text;
                cd.CommandText = strSQL;
                cd.ExecuteNonQuery();
                //}
                strRet = "OK";
                //st.Commit();
            }
            catch (Exception e)
            {
                // st.Rollback();
                //throw new CEventLog(strSQL + "更新数据表报错!", e);
                strRet = e.Message;
                MessageBox.Show(e.Message);
            }
            finally
            {
                sc.Close();
            }
            return strRet;
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            strConn = AmateParam.Configuration.GetConnString("", "Master");
            if (PublicFunc.sLanguage != "CN")
            {
                this.Text = "Data Management";
                sInfo = "Processing Database {0} . . .";
                button1.Text = "Backup Database";
                button2.Text = "Restore Database";
                label1.Text = "Prepare for Database operation ... ";
            }
        }

        private string GetUniformStr(string str, string sKey)
        {
            if ((str == "") || (sKey == "")) return "";
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();

            byte[] inputByteArray = new byte[str.Length / 2];
            for (int x = 0; x < str.Length / 2; x++)
            {
                int i = (Convert.ToInt32(str.Substring(x * 2, 2), 16));
                inputByteArray[x] = (byte)i;
            }

            des.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
            des.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();

            StringBuilder ret = new StringBuilder();

            return System.Text.Encoding.Default.GetString(ms.ToArray());
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            if ((progressBar1.Value + 8) > 100)
            {
                timer1.Enabled = false;
                progressBar1.Value = 100;
            }
            else
                progressBar1.Value = progressBar1.Value + 8;
        }

        string sDelSP = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[StopLogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) " +
                "DROP PROCEDURE [dbo].[StopLogin] ";
        string sCreateSP =
                "CREATE PROCEDURE StopLogin " +
                " @Dname varchar(50) " +
                "AS " +
                "DECLARE " +
                " @name varchar(50),  " +
          "@s varchar(1000) " +
       "BEGIN " +
          "IF (@Dname = '') " +
          "BEGIN " +
            "DECLARE DataName CURSOR FOR " +
            "SELECT name FROM sysdatabases WHERE name not in ('master') " +
            "OPEN DataName " +
           "FETCH NEXT FROM DataName " +
            "INTO @name " +
            "WHILE (@@FETCH_STATUS = 0) " +
            "BEGIN " +
               "DECLARE tb CURSOR local " +
               "FOR " +
               "SELECT N'kill   '+CAST(spid AS varchar) " +
               "FROM master..sysprocesses " +
               "WHERE dbid=db_id(@name) " +
               "OPEN tb " +
               "FETCH next FROM tb INTO @s " +
               "WHILE @@FETCH_STATUS=0 " +
               "BEGIN " +
               "EXEC(@s) " +
               "FETCH NEXT FROM tb INTO @s " +
               "END " +
               "CLOSE tb " +
               "DEALLOCATE tb " +
               "FETCH NEXT FROM DataName  " +
               "INTO @name " +
            "END " +
            "CLOSE DataName " +
            "DEALLOCATE DataName " +
          "END " +
          "ELSE " +
          "BEGIN " +
           " DECLARE tb CURSOR local " +
           "FOR " +
           "SELECT N'kill   '+CAST(spid AS varchar) " +
           "FROM master..sysprocesses " +
           "WHERE dbid=db_id(@Dname) " +
           "OPEN tb " +
           "FETCH next FROM tb INTO @s  " +
           "WHILE @@FETCH_STATUS=0 " +
          " BEGIN " +
               "EXEC(@s) " +
               "FETCH NEXT FROM tb INTO @s " +
           "END " +
           "CLOSE tb " +
           "DEALLOCATE tb  " +
            "END " +
         "END";

        //private void button3_Click(object sender, EventArgs e)
        //{
        //    ExcuteSQL(ss);
        //}
    }
}
